﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBEngine.Elements;
using System.Data;
using System.Data.SqlClient;
namespace DBEngine.Tables
{
    abstract class ExaminationTable : Table
    {
        protected Dictionary<Examination.Fields, SqlDbType> mTableFields;

        protected ExaminationTable(string mTableName, Dictionary<Examination.Fields, SqlDbType> mTableFields) :base(mTableName)
        {
            this.mTableFields = mTableFields;
            this.mTableFields.Add(Examination.Fields.SPHERE, SqlDbType.Float);
            this.mTableFields.Add(Examination.Fields.CYLINDER, SqlDbType.Float);
            this.mTableFields.Add(Examination.Fields.AXIS, SqlDbType.Float);
            this.mTableFields.Add(Examination.Fields.ID, SqlDbType.Int);
            this.mTableFields.Add(Examination.Fields.ClientID, SqlDbType.Int);
            this.mTableFields.Add(Examination.Fields.Date, SqlDbType.SmallDateTime);
        }
        override protected string SetInsertQuery(Element newElement, string cols, string values, List<SqlParameter> paramsList)
        {
            Examination examination = (Examination)newElement;
            string retval = "";

            int i = 0;
            string paramName;
            Examination.Fields colName;
            SqlParameter param;
            foreach (KeyValuePair<Examination.Fields, SqlDbType> pair in mTableFields)
            {
                colName = pair.Key;
                paramName = @"@" + colName + "Param";

                if (examination[colName] == null || colName == Examination.Fields.ID)
                    continue;

                if (i++ > 0)
                {
                    cols += ", ";
                    values += ", ";
                }
                if (colName == Examination.Fields.ID || colName == Examination.Fields.ClientID || colName == Examination.Fields.Date)
                {
                    cols += colName;
                    values += paramName;
                    param = new SqlParameter(paramName, pair.Value);
                    param.Value = examination[colName];
                    paramsList.Add(param);
                }
                else
                {
                    int j = 0;
                    foreach (ExaminationElement.Fields field in Enum.GetValues(typeof(ExaminationElement.Fields)))
                    {
                        if (j++ > 0)
                        {
                            cols += ", ";
                            values += ", ";
                        }
                        cols += colName + "_" + field;
                        values += paramName + "_" + field;
                        param = new SqlParameter(paramName + "_" + field, pair.Value);
                        param.Value = ((ExaminationElement)(examination[colName]))[field];
                        paramsList.Add(param);

                    }
                }
            }
            cols += ")";
            values += ")";
            retval += cols + " " + values + @"; Select @@IDENTITY";

            return retval;
        }
   
        override protected string SetUpdateQuery(Element element, List<SqlParameter> paramsList)
        {
            Examination examination = (Examination)element;
            string retval = "";
            string updateString = "";
            
            int i = 0;
            Examination.Fields colName;
            string paramName;
            SqlParameter param;
            foreach (KeyValuePair<Examination.Fields, SqlDbType> pair in mTableFields)
            {
                colName = pair.Key;
                paramName = @"@" + colName + "Param";

                if (examination[colName] == null || colName == Examination.Fields.ID)
                    continue;
                if (i == 0)
                {
                    updateString += "Set ";
                }
                if (i++ > 0)
                {
                    updateString += ", ";
                }

                if (colName == Examination.Fields.ID || colName == Examination.Fields.ClientID || colName == Examination.Fields.Date)
                {
                    updateString += colName + " = " + paramName;
                    param = new SqlParameter(paramName, pair.Value);
                    param.Value = examination[colName];
                    paramsList.Add(param);
                }
                else
                {
                    int j = 0;
                    foreach (ExaminationElement.Fields field in Enum.GetValues(typeof(ExaminationElement.Fields)))
                    {
                        if (j++ > 0)
                        {
                            updateString += ", ";
                        }
                        updateString += colName + "_" + field + " = " + paramName + "_" + field;
                        param = new SqlParameter(paramName + "_" + field, pair.Value);
                        param.Value = ((ExaminationElement)(examination[colName]))[field];
                        paramsList.Add(param);

                    }
                }
            }

            retval = updateString ;
            
            return retval;
        }

        //      override internal Boolean Insert(Object newObj)       
        //     {
        //Examination examination = (Examination)newObj;
        //Boolean retval = false;

        //if (examination == null)
        //    return retval;


        //string queryString = @"Insert into " + mTableName + " ";
        //string cols = "(", values = "Values(";
        //List<SqlParameter> paramsList = new List<SqlParameter>();

        //int i = 0;
        //string paramName;
        //Examination.Fields colName;
        //SqlParameter param;
        //foreach (KeyValuePair<Examination.Fields, SqlDbType> pair in mTableFields)
        //{


        //    colName = pair.Key;
        //    paramName = @"@" + colName + "Param";

        //    if (examination[colName] == null || colName == Examination.Fields.ID)
        //        continue;

        //    if (i++ > 0)
        //    {
        //        cols += ", ";
        //        values += ", ";
        //    }
        //    if (colName == Examination.Fields.ID || colName == Examination.Fields.ClientID || colName == Examination.Fields.Date)
        //    {
        //        cols += colName;
        //        values += paramName;
        //        param = new SqlParameter(paramName, pair.Value);
        //        param.Value = examination[colName];
        //        paramsList.Add(param);
        //    }
        //    else
        //    {
        //        int j = 0;
        //        foreach (ExaminationElement.Fields field in Enum.GetValues(typeof(ExaminationElement.Fields)))
        //        {
        //            if (j++ > 0)
        //            {
        //                cols += ", ";
        //                values += ", ";
        //            }
        //            cols += colName + "_" + field;
        //            values += paramName + "_" + field;
        //            param = new SqlParameter(paramName + "_" + field, pair.Value);
        //            param.Value = ((ExaminationElement)(examination[colName]))[field];
        //            paramsList.Add(param);

        //        }
        //    }


        //}
        //cols += ")";
        //values += ")";
        //queryString += cols + " " + values + @"; Select @@IDENTITY";

        //SqlCommand command = DBConnection.getCommand();
        //command.CommandText = queryString;
        //foreach (SqlParameter parameter in paramsList)
        //{
        //    command.Parameters.Add(parameter);
        //}

        //Object dbResult = command.ExecuteScalar();
        //if (dbResult == null)
        //    return retval;

        //retval = true;
        //examination[Examination.Fields.ID] = dbResult;


        //return retval;
        //    }
     //   override internal Boolean Update(Object newObj)
     //   {
        // {
        //     Examination examination = (Examination)newObj;
        //    Boolean retval = false;

        //    if (examination == null)
        //        return retval;


        //    string queryString = @"Update  " + mTableName + " ";
        //    string updateString = "";
        //    List<SqlParameter> paramsList = new List<SqlParameter>();

        //    int i = 0;
        //    Examination.Fields colName;
        //    string paramName;
        //    SqlParameter param;
        //    foreach (KeyValuePair<Examination.Fields, SqlDbType> pair in mTableFields)
        //    {


        //        colName = pair.Key;
        //        paramName = @"@" + colName + "Param";

        //        if (examination[colName] == null || colName == Examination.Fields.ID)
        //            continue;
        //        if (i == 0)
        //        {
        //            updateString += "Set ";
        //        }
        //        if (i++ > 0)
        //        {
        //            updateString += ", ";
        //        }

        //        if (colName == Examination.Fields.ID || colName == Examination.Fields.ClientID || colName == Examination.Fields.Date)
        //        {
        //            updateString += colName + " = " + paramName;
        //            param = new SqlParameter(paramName, pair.Value);
        //            param.Value = examination[colName];
        //            paramsList.Add(param);
        //        }
        //        else
        //        {
        //            int j = 0;
        //            foreach (ExaminationElement.Fields field in Enum.GetValues(typeof(ExaminationElement.Fields)))
        //            {
        //                if (j++ > 0)
        //                {
        //                    updateString += ", ";
        //                }
        //                updateString += colName + "_" + field + " = " + paramName + "_" + field;
        //                param = new SqlParameter(paramName + "_" + field, pair.Value);
        //                param.Value = ((ExaminationElement)(examination[colName]))[field];
        //                paramsList.Add(param);

        //            }
        //        }

                

        //    }

        //    queryString += updateString + "  where ID =@IDParam";
        //    param = new SqlParameter("@IDParam", mTableFields[Examination.Fields.ID]);
        //    param.Value = examination[Examination.Fields.ID];
        //    paramsList.Add(param);

        //    SqlCommand command = DBConnection.getCommand();
        //    command.CommandText = queryString;
        //    foreach (SqlParameter parameter in paramsList)
        //    {
        //        command.Parameters.Add(parameter);
        //    }

        //    command.ExecuteNonQuery();
        //    return true;
        //}

        //internal  Examination Retrive(int id)
        //{

        //    Examination retval = null;
        //    string queryString = @"Select * From " + mTableName + " Where ID =@IDParam ";

        //    SqlParameter param = new SqlParameter("@IDParam", mTableFields[Examination.Fields.ID]);
        //    param.Value = id;

        //    SqlCommand command = DBConnection.getCommand();
        //    command.CommandText = queryString;
        //    command.Parameters.Add(param);

        //    SqlDataReader reader = command.ExecuteReader();
        //    while (reader.Read())
        //    {
        //        retval = (Examination)ReadRow(reader);
        //    }
        //     reader.Close();
        //    return retval;
        //}

     
    }
}
